USE master;
GO

-- pre-size your database to reduce auto-growth operations
ALTER DATABASE AdventureWorksDW
	MODIFY FILE (NAME = N'AdventureWorksDW', 
	SIZE = 2048MB, FILEGROWTH = 1024MB);
GO

ALTER DATABASE AdventureWorksDW
	MODIFY FILE (NAME = N'AdventureWorksDW_Log', 
	SIZE = 1024MB, FILEGROWTH = 512MB);
GO

ALTER DATABASE AdventureWorksDW SET RECOVERY SIMPLE;
GO

USE AdventureWorksDW;
GO

-- create a big version of the FactInternetSales table
CREATE TABLE dbo.FactInternetSalesPtn (
    ProductKey int NOT NULL,
	OrderDateKey int NOT NULL,
	DueDateKey int NOT NULL,
	ShipDateKey int NOT NULL,
	CustomerKey int NOT NULL,
	PromotionKey int NOT NULL,
	CurrencyKey int NOT NULL,
	SalesTerritoryKey int NOT NULL,
	SalesOrderNumber nvarchar(20) NOT NULL,
	SalesOrderLineNumber tinyint NOT NULL,
	RevisionNumber tinyint NOT NULL,
	OrderQuantity smallint NOT NULL,
	UnitPrice money NOT NULL,
	ExtendedAmount money NOT NULL,
	UnitPriceDiscountPct float NOT NULL,
	DiscountAmount float NOT NULL,
	ProductStandardCost money NOT NULL,
	TotalProductCost money NOT NULL,
	SalesAmount money NOT NULL,
	TaxAmt money NOT NULL,
	Freight money NOT NULL,
	CarrierTrackingNumber nvarchar(25) NULL,
	CustomerPONumber nvarchar(25) NULL,
	OrderDate datetime NULL,
	DueDate datetime NULL,
	ShipDate datetime NULL
) ON [PRIMARY];
GO

-- create the partition function
CREATE PARTITION FUNCTION pfDateRange(INT)
AS RANGE RIGHT FOR VALUES(20050101, 20060101, 20070101, 20080101);
GO

-- and now the partition scheme
CREATE PARTITION SCHEME psDateRange
AS PARTITION pfDateRange ALL TO ([PRIMARY]);
GO

-- create the clustered index against the partiton scheme and use data compression PAGE
CREATE CLUSTERED INDEX CIX_FactInternetSalesPtn_OrderDateKey
ON dbo.FactInternetSalesPtn (OrderDateKey ASC)
WITH (DATA_COMPRESSION = PAGE) ON psDateRange(OrderDateKey);
GO

-- copy the data from the FactInternetSales into the new table
SET NOCOUNT ON;

INSERT INTO dbo.FactInternetSalesPtn WITH(TABLOCKX)
SELECT * FROM dbo.FactInternetSales;
GO

-- increasing the number of rows
INSERT INTO dbo.FactInternetSalesPtn WITH(TABLOCKX)
	(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate)
SELECT
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)), 20) AS SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
FROM dbo.FactInternetSalesPtn;
CHECKPOINT;
GO 2 -- change this based on your test environment storage and time you want to wait

-- update the statistics with fullscan
UPDATE STATISTICS dbo.FactInternetSalesPtn(CIX_FactInternetSalesPtn_OrderDateKey)
WITH FULLSCAN;

-- inspect the tables
EXECUTE sp_helpindex 'FactInternetSalesPtn';
GO

-- check the number of records in the FactInternetSalesPtn table
SELECT
	FORMAT(COUNT(*), '#,##') AS row_num
FROM dbo.FactInternetSalesPtn;

-- list out which tables are partitioned, and what partition scheme and partition function they use
SELECT
	t.name AS table_name,
	ps.name AS partition_scheme, 
	ps.data_space_id,
	pf.name AS partition_function,
	pf.function_id
FROM sys.tables t
	INNER JOIN sys.indexes i ON t.object_id = i.object_id
	INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
	INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id < 2;

-- for a given partition function, list each of the boundary values
SELECT
	prv.boundary_id,
	prv.value
FROM sys.partition_range_values prv
	INNER JOIN sys.partition_functions pf ON prv.function_id = pf.function_id
WHERE pf.name = 'pfDateRange'		-- partition function name
ORDER BY prv.value;

-- verify the partitioned indexes in the table
SELECT
	OBJECT_NAME(ips.[object_id]) AS 'object_name',
	i.name AS index_name,
	ips.index_id,
	ips.partition_number,
	ips.index_type_desc,
	ips.alloc_unit_type_desc,
	ips.page_count
FROM sys.dm_db_index_physical_stats (
	DB_ID('AdventureWorksDW'),
	OBJECT_ID(N'dbo.FactInternetSalesPtn'),
	NULL,
	NULL,
	'LIMITED') ips
CROSS APPLY AdventureWorksDW.sys.indexes i
WHERE i.object_id = ips.object_id
AND i.index_id = ips.index_id
ORDER BY index_name, partition_number ASC;

-- show the table partitions and rows inserted in the partitions
SELECT
	p.partition_number,
	fg.name,
	p.rows,
	p.index_id,
	au.total_pages,
	au.used_pages
FROM sys.partitions p
    INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('dbo.FactInternetSalesPtn')
AND p.index_id < 2;

-- and now action...

-- add the actual execution plan (Ctrl+M)

-- online rebuild only partition 5
-- Wait 0 minutes (execute immediately) and kill all blockers.
ALTER INDEX CIX_FactInternetSalesPtn_OrderDateKey
ON dbo.FactInternetSalesPtn REBUILD PARTITION = 5 
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes,
	ABORT_AFTER_WAIT = BLOCKERS)));

-- Wait for up to 15 minutes to execute.
-- If the Sch-M lock cannot be granted within this timeframe, terminate the DDL statement.
ALTER INDEX CIX_FactInternetSalesPtn_OrderDateKey
ON dbo.FactInternetSalesPtn REBUILD PARTITION = 5 
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 15 minutes,
	ABORT_AFTER_WAIT = SELF)));

-- look at the Online Index Insert operator
-- Physical Operation: Online Index Insert

-- cleanup
/*
USE AdventureWorksDW;
GO

IF OBJECTPROPERTY(OBJECT_ID(N'dbo.FactInternetSalesPtn'), 'IsUserTable') IS NOT NULL
	DROP TABLE dbo.FactInternetSalesPtn;

IF EXISTS (SELECT * FROM sys.partition_schemes
    WHERE name = 'psDateRange')
	DROP PARTITION SCHEME psDateRange;

IF EXISTS (SELECT * FROM sys.partition_functions
    WHERE name = 'pfDateRange')
	DROP PARTITION FUNCTION pfDateRange;
*/